| View previous topic :: View next topic |
| Author |
Message |
Urlord
Joined: 17 Nov 2006
Posts: 122
|
 Posted: Fri Sep 28, 2007 20:58 Post subject: MySQL Stored Proceedure |
 |
|
I was wondering if I could possibly get a little help. Though I am fine at writing SQL Queries, I have not had much experience writing Stored Procs.
I am hoping that one of you could advise me.
I am working on my custom merchant system and I need a stored proc to adjust a store's inventory from time to time. I am using MySQL 5.0.
Here is what I have: | Code: | The VAR sStoreTag is being passed into the Stored Proceedure
Cycle through each record in:
SELECT si.ItemResRef, si.QtyOnHand, si.MinQty, si.MaxQty, i.GPValue
FROM nym_store_inventory si, nym_items i
WHERE si.StoreTag = sStoreTag
AND si.ItemResRef = i.ResRef
For Each Record
if(QtyOnHand < MinQty)
UPDATE nym_store_inventory SET QtyOnHand = MinQty
else if(QtyOnHand >= MaxQty && MinQty < MaxQty)
UPDATE nym_store_inventory SET QtyOnHand = ((MaxQty-MinQty)/2)+MinQty
else if(QtyOnHand > 0 && MinQty == 0 && d10 == 1)
UPDATE nym_store_inventory SET QtyOnHand = 0
else if(QtyOnHand == 0 && GPValue < 1000 && random(10) == 1)
UPDATE nym_store_inventory SET QtyOnHand = 1
else if(QtyOnHand == 0 && GPValue >= 1000 && GPValue < 10000 && random(100) == 1)
UPDATE nym_store_inventory SET QtyOnHand = 1
else if(QtyOnHand == 0 && GPValue >= 10000 && random(1000) == 1)
UPDATE nym_store_inventory SET QtyOnHand = 1
|
If this is not appropriate for this forum, just delete it and I'm sorry.
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri |
|
| Back to top |
|
 |
Urlord
Joined: 17 Nov 2006
Posts: 122
|
| Posted: Fri Sep 28, 2007 22:11 Post subject: |
|
|
Here is what I have put together from reading Stored Proc examples on the MySQL Documentation site.
What is wrong with it? Because it isn't updating the QtyOnHand values.
Also, is there a way to output debug values, so I can see what it is doing?
| Code: | DELIMITER $$
DROP PROCEDURE IF EXISTS `nymri2`.`sp_nym_store_adjust_inventory` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_nym_store_adjust_inventory`(
IN sStoreTag varchar(64)
)
BEGIN
DECLARE sItemResRef varchar(64);
DECLARE nRowRows, nRowCount, nQtyOnHand, nMinQty, nMaxQty, nGPValue int;
DECLARE InventoryCUR CURSOR FOR SELECT si.ItemResRef, si.QtyOnHand, si.MinQty, si.MaxQty, i.GPValue
FROM nym_store_inventory si, nym_items i
WHERE si.StoreTag = sStoreTag
AND si.ItemResRef = i.ResRef;
OPEN InventoryCUR;
SELECT COUNT(*) INTO nRowCount FROM nym_store_inventory WHERE si.StoreTag = sStoreTag;
SET nRowCount = nRowRows;
WHILE nRowCount > 0 DO
FETCH InventoryCUR INTO sItemResRef,nQtyOnHand,nMinQty,nMaxQty,nGPValue;
# Increase QtyOnHand to at least MinQty
IF nQtyOnHand < nMinQty THEN
UPDATE nym_store_inventory SET QtyOnHand = nMinQty WHERE ItemResRef = sItemResRef;
# Decrease QtyOnHand if it is at MaxQty
ELSEIF nQtyOnHand >= nManQty AND nMinQty < nMaxQty THEN
UPDATE nym_store_inventory SET QtyOnHand = ((nMaxQty-nMinQty)/2)+MinQty WHERE ItemResRef = sItemResRef;
# A 10% Chance of loosing items not normally stocked
ELSEIF nQtyOnHand > 0 AND nMinQty = 0 AND FLOOR(1 + (RAND() * 10)) = 1 THEN
UPDATE nym_store_inventory SET QtyOnHand = 0 WHERE ItemResRef = sItemResRef;
# A 10% Chance of having a special item under 1000 GPValue
ELSEIF nQtyOnHand = 0 AND nGPValue < 1000 AND FLOOR(1 + (RAND() * 10)) = 1 THEN
UPDATE nym_store_inventory SET QtyOnHand = 1 WHERE ItemResRef = sItemResRef;
# A 1% Chance of having a special item between 1000 and 10000 GPValue
ELSEIF nQtyOnHand = 0 AND nGPValue >= 1000 AND nGPValue < 10000 AND FLOOR(1 + (RAND() * 100)) = 1 THEN
UPDATE nym_store_inventory SET QtyOnHand = 1 WHERE ItemResRef = sItemResRef;
# A 0.1% Chance of having a special item equal to or more than 10000 GPValue
ELSEIF nQtyOnHand = 0 AND nGPValue >= 10000 AND FLOOR(1 + (RAND() * 1000)) = 1 THEN
UPDATE nym_store_inventory SET QtyOnHand = 1 WHERE ItemResRef = sItemResRef;
END IF;
SET nRowCount = nRowCount - 1;
END WHILE;
CLOSE InventoryCUR;
END $$
DELIMITER ; |
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Fri Sep 28, 2007 23:37 Post subject: |
|
|
Don't you get some compilation error?
2nd IF -> nQtyOnHand >= nManQty
nManQty doesn't exist (my quess is that it should be nMaxQty)
| Code: | | ELSEIF nQtyOnHand >= nMaxQty THEN |
| Code: | DELIMITER $$
DROP PROCEDURE IF EXISTS `nymri2`.`sp_nym_store_adjust_inventory` $$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `sp_nym_store_adjust_inventory`( IN sStoreTag varchar(64) )
BEGIN
DECLARE sItemResRef varchar(64);
DECLARE fRandom FLOAT(1,4);
DECLARE NotFound TINYINT DEFAULT 0;
DECLARE newQty INTEGER DEFAULT 0;
DECLARE nRowCount,
nQtyOnHand,
nMinQty,
nMaxQty,
nGPValue int;
DECLARE InventoryCUR CURSOR FOR
SELECT si.ItemResRef,
si.QtyOnHand,
si.MinQty,
si.MaxQty,
i.GPValue
FROM nym_store_inventory si, nym_items i
WHERE si.StoreTag = sStoreTag
AND si.ItemResRef = i.ResRef;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NotFound = 1;
OPEN InventoryCUR;
scan:LOOP
FETCH InventoryCUR INTO
sItemResRef,
nQtyOnHand,
nMinQty,
nMaxQty,
nGPValue;
IF NotFound = 1
THEN LEAVE scan;
END IF;
SET fRandom = RAND();
CASE
WHEN nQtyOnHand < nMinQty THEN SET newQty = nMinQty;
WHEN nQtyOnHand => nMaxQty THEN SET newQty = ((nMaxQty-nMinQty)/2)+MinQty;
WHEN nQtyOnHand > 0 AND nMinQty = 0 AND fRandom < 0.01 THEN SET newQty = 0;
WHEN nQtyOnHand = 0 THEN
CASE
WHEN nGPValue > 10000 AND fRandom < 0.001 THEN SET newQty = 1;
WHEN nGPValue > 1000 AND fRandom < 0.01 THEN SET newQty = 1;
WHEN nGPValue <= 1000 AND fRandom < 0.1 THEN SET newQty = 1;
ELSE SET newQty = 0;
END CASE;
ELSE INTERATE scan;
END CASE;
UPDATE nym_store_inventory
SET QtyOnHand = newQty
WHERE ItemResRef = sItemResRef;
END LOOP scan;
CLOSE InventoryCUR;
END $$
DELIMITER ; |
Hopefully all the logic is intact...
New stuff.
DECLARE CONTINUE - OnError Continue
CASE *determines the new Qty value [only one instance of UPDATE statement]
LEAVE - Exit loop *note the loop labels
INTERATE - continue (start loop again) *No change - do nothing
Hmmm,
Not sure that this is the best approach - you could be using a series of bulk updates to achieve the same ends with a lot less code (and execute quicker)
It would make a big difference if you were writing back to the cursor Qty (an update cursor)
Cheers
Gryphyn
Last edited by Gryphyn on Sat Sep 29, 2007 5:05; edited 1 time in total |
|
| Back to top |
|
|
Urlord
Joined: 17 Nov 2006
Posts: 122
|
| Posted: Sat Sep 29, 2007 4:38 Post subject: |
|
|
Thanks a bunch Gryphyn! 
With the newer code you provided (after a little tweaking) its working very well. The average time for this stroed proc to run (after 100 calls) is 1368 micro-seconds. This code runs once per store every real hour after the store is opened for the first time. Since it is running on the DB, outside NWN2 and on a different processor, I don't expect to see any lag from it at all. I only get a tiny bit of lag when you open a store in game after it's inventory has been adjusted by the stored proc. But again, that is very minor and only once per hour at most.
The logic is working well too. I can see special items pop up in the store, stay for a bit and then go away. Also, the store's common inventory is staying well stocked.
Thanks again.
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sat Sep 29, 2007 5:02 Post subject: |
|
|
| Urlord wrote: | | The average time for this stored proc to run (after 100 calls) is 1368 micro-seconds. |
Ouch! but if you're happy with it.
Out of curiosity,
What's the average row count for each CURSOR?
How do you detect that there's been a change?
Or are you just unloading/reloading the store every hour?
--if the later, add your SELECT to the end of the stored proc.
and SQLFetch() can be used to get the new store info at the same time.
The following should be (a lot) quicker
| Code: | DELIMITER $$
DROP PROCEDURE IF EXISTS `nymri2`.`sp_nym_adjust_inventory` $$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `sp_nym_adjust_inventory`()
BEGIN
DECLARE random FLOAT(1,4);
UPDATE inv
SET random = RAND(),
inv.QtyOnHand =
CASE
WHEN inv.QtyOnHand < inv.MinQty THEN inv.MinQty
WHEN inv.QtyOnHand >= inv.MaxQty THEN ((inv.MaxQty-inv.MinQty)/2)+inv.MinQty
WHEN inv.QtyOnHand > 0 AND inv.MinQty > 0 AND random < 0.01 THEN 0
WHEN inv.QtyOnHand = 0 THEN
CASE
WHEN itm.GPValue > 10000 AND random < 0.001 THEN 1
WHEN itm.GPValue > 1000 AND itm.GPValue <= 10000 AND random < 0.01 THEN 1
WHEN itm.GPValue <= 1000 AND random < 0.1 THEN 1
ELSE 0
END
ELSE inv.QtyOnHand
END
FROM nym_store_inventory inv, nym_items itm
WHERE inv.ItemResRef = itm.ResRef;
END $$
DELIMITER ; |
NB: I changed it to do EVERY store, if you really need it to be store specific it's easy to add in a WHERE condition to only work for a specific store
Cheers
Gryphyn |
|
| Back to top |
|
|
Urlord
Joined: 17 Nov 2006
Posts: 122
|
| Posted: Sat Sep 29, 2007 9:51 Post subject: |
|
|
First off, thanks for taking the time to assist me. I really appreciate it.
To answer your question about updating the store. When a NPC merchant is asked to open their store, the store is created at the location of the NPC. The store is then Initialized. The last part of the Initialization is to stock the store based on data (ItemResRef, QryOnHand, SellStackSize) from the database. It then does a DelayCommand for 7200.0 seconds to call the DeleteAndUpdate function.
The DeleteAndUpdate function deletes the store object and calls this stored proceedure. The next time the store is opened, it start the process all over. If you can suggest a more efficient way, I am all ears.
I tried the latest code you posted (it looks a lot faster), but it doesn't compile.
First, it complained about float(1,4). I had to fix that in the first code too. The first number (D - Digits) must be >= the second number (P=Precision). I changed it to (5,4) for a 0.0000 number.
Now it is giving the following error: | Code: |
Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE inv
SET random = RAND(),
inv.QtyOnHand =
CASE
' at line 7 |
I am using Query Browser 1.2.6 Beta and MySQL 5.0 (community edition).
I cant figure out what is wrong. Does it compile for you? Here is the SQL to create the tables used:
Bed time for me - More tomorrow! | Code: | DROP TABLE IF EXISTS `nymri2`.`nym_store_inventory`;
CREATE TABLE `nymri2`.`nym_store_inventory` (
`StoreTag` varchar(64) NOT NULL,
`ItemResRef` varchar(64) NOT NULL default '',
`MinQty` int(10) unsigned NOT NULL default '0',
`MaxQty` int(10) unsigned NOT NULL default '0',
`QtyOnHand` int(10) unsigned NOT NULL,
`SellStackSize` int(10) unsigned NOT NULL,
PRIMARY KEY (`StoreTag`,`ItemResRef`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
| Code: | DROP TABLE IF EXISTS `nymri2`.`nym_items`;
CREATE TABLE `nymri2`.`nym_items` (
`ResRef` varchar(64) NOT NULL,
`Tag` varchar(64) NOT NULL,
`Name` varchar(64) NOT NULL default '',
`BaseItemType` int(10) unsigned NOT NULL default '0',
`StackSize` int(10) unsigned NOT NULL default '1',
`GPValue` int(11) NOT NULL default '1',
`Category` varchar(255) NOT NULL default '0',
`Verified` int(11) NOT NULL default '0',
PRIMARY KEY (`ResRef`,`Tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sat Sep 29, 2007 10:13 Post subject: |
|
|
| Urlord wrote: | First off, thanks for taking the time to assist me. I really appreciate it.
I tried this latest code (it looks a lot faster) but it doesn't like it.
First, it complained about float(1,4). I had to fix that in the first code. The first number (D - Digits) must be >= the second number (P=Precision). I changed it to (5,4) for a 0.0000 number.
Now it is giving the following error: | Code: |
Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE inv
SET random = RAND(),
inv.QtyOnHand =
CASE
' at line 7 |
I am using Query Browser 1.2.6 Beta and MySQL 5.0 (community edition).
I cant figure out what is wrong.
Bed time for me - More tomorrow! |
Float stuff noted.
remove "random = RAND()," so it's just "SET inv.QtyOnHand ="
replace each (further) "random" with "RAND()"
the DECLARE is no longer required
Sorry, thought the syntax extended to pre-setting a local (so RAND() is only calculated once per row) - I mostly use SQLServer where that would be valid.
At any rate you see that the whole thing can be done via a single UPDATE.
Cheers
Gryphyn |
|
| Back to top |
|
|
Urlord
Joined: 17 Nov 2006
Posts: 122
|
| Posted: Sat Sep 29, 2007 18:40 Post subject: |
|
|
Okay - I got the single-update versions working.
You cannot have a FROM clause in an UPDATE statement in MySQL.
Here is the final SP: | Code: | DELIMITER $$
DROP PROCEDURE IF EXISTS `nymri2`.`sp_nym_store_adjust_inventory` $$
CREATE PROCEDURE `sp_nym_store_adjust_inventory`(
IN sStoreTag varchar(64)
)
BEGIN
UPDATE nym_store_inventory inv, nym_items itm
SET inv.QtyOnHand =
CASE
WHEN inv.QtyOnHand > 0 AND inv.MinQty = 0 AND RAND() < 0.1 THEN 0
WHEN inv.QtyOnHand = 0 THEN
CASE
WHEN itm.GPValue > 100000 AND RAND() < 0.0001 THEN 1
WHEN itm.GPValue > 10000 AND itm.GPValue <= 100000 AND RAND() < 0.001 THEN 1
WHEN itm.GPValue > 1000 AND itm.GPValue <= 10000 AND RAND() < 0.01 THEN 1
WHEN itm.GPValue <= 1000 AND RAND() < 0.1 THEN 1
ELSE 0
END
WHEN inv.QtyOnHand < inv.MinQty THEN inv.MinQty
WHEN inv.QtyOnHand >= inv.MaxQty THEN ((inv.MaxQty-inv.MinQty)/2)+inv.MinQty
ELSE inv.QtyOnHand
END
WHERE inv.ItemResRef = itm.ResRef
AND inv.StoreTag = sStoreTag;
SELECT ItemResRef, SellStackSize, QtyOnHand
FROM nym_store_inventory
WHERE StoreTag = sStoreTag
AND QtyOnHand > 0;
END $$
DELIMITER ; |
I decided to make it return a item list so that I can use it to return items to stock in the store.
WORKS GREAT!
Average time for 80-120 rows returned for a single store is about 0.00015 seconds.
There are currently 1850 total rows in the nym_store_inventory table.
This is 10% of the previous time.
That gets the inventory update and retrieval down to a very acceptable time. Not to look at my Store Stocking routines and see where I can make improvements. If anyone wants to chime in with suggestions, they would be well recieved. It is pretty straight forward, but here is the NWN2 Script for stocking the store:
| Code: |
void ResetNymStoreInventory(object oStore)
{
string sStoreTag = GetTag(oStore);
string sSQL, sID, sItemResRef;
int SellStackSize, QtyOnHand, i;
sSQL = "call sp_nym_store_adjust_inventory('"+GetTag(oStore)+"')";
SQLExecDirect(sSQL);
while(SQLFetch() != SQL_ERROR) {
sItemResRef = SQLGetData(1);
SellStackSize = StringToInt(SQLGetData(2));
QtyOnHand = StringToInt(SQLGetData(3));
for(i=0; i<QtyOnHand; i=i+SellStackSize) {
if(i+SellStackSize <= QtyOnHand)
CreateItemOnObject(sItemResRef, oStore, SellStackSize);
else
CreateItemOnObject(sItemResRef, oStore, QtyOnHand-i);
}
}
StoreDebug("Store ("+GetTag(oStore)+") Inventory Restocked.");
} |
Thanks Gryphyn.
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sun Sep 30, 2007 0:11 Post subject: |
|
|
Me again...
Personally I wouldn't have that relationship between Stack & Qty. You have a Qty of some Item - the item can be "arrows (n)" - so for 1000 arrows, you might have Qty 20, Stack 50.
If I'm reading your code right you could end up with 1 short stack of an item (chuck-out bin?)
Use the info from the 2DA's to get blueprint-stacksize. if it's 1 you don't have to do anything, more than 1 and you can make individual adjustments (or not)
And on timing...
| Quote: | | Average time for 80-120 rows returned for a single store is about 0.00015 seconds. |
This is almost fast enough that you could, DELETEandUPDATE whenever the stores OnClose() kicks in - the DelayCommand() might occasionally catch someone with a store open (and be near impossible to debug), but then again it might open an exploit (instant restock)
Cheers
Gryphyn |
|
| Back to top |
|
|
Urlord
Joined: 17 Nov 2006
Posts: 122
|
| Posted: Sun Sep 30, 2007 4:17 Post subject: |
|
|
Actually, I don't want the store to update more than once per game day. Some stores update even less often. It helps to control the craft-sell cycle and forces PCs to find other PCs to sell their crafted goods to or craft a variety of things. Also, it only allows for special items (MinQty=0) to show up no more than once per game day if at all.
About the SellStackSize. I need this because some items stack, but are too expensive to sell in stacks, like potions. I have the SellStackSize set to 1 for potions even though the 2da stack limit is 10. I had it the other way to begin with.
Now, if when you bought an iten, it didn't base the "You cannot afford this" calculation on the value of the whole stack and based it on the value of a single item, then it would be fine to stack them using the 2da limit. But I cannot find where to change the logic for that.
Anyway, I love the way it is working right now. Exactly what I wanted. And faster than I ever thought it would be.
Thanks so much for your expertise!!!
_________________
Jim (aka, Urlord)
Visit the Persistent World of Nymri |
|
| Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Sun Sep 30, 2007 5:17 Post subject: |
|
|
Yep,
Going to have to pinch it 
Cheers
Gryphyn |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
