NWNX-SQL-Scripting intro

[Brannor]

I asked Pap to give me an intro into his NWNX-SQL-Code for use in the crafting system. Since many have declared interrest in joining I thought it might be good to have it in the IRC channel so others can listen in as well.

20:00 gmt+1
the Avlis irc channel

ONLY Pap or myself should be speaking during the event.
Pap or myself will discontinue the intor occasionally for questions.

I will post the log from the intro here after completion.

Hope to see you there.

[Belasco_old]

Brannor,

What date is the meeting taking place? I was just there and no meeting. I would love to attend this and hear what the team is thinking and working on in crafting.

Belasco

[Papillon]

It should have been today, but unfortunately, my DSL connection went down for several hours and so I couldn't attend.

I'd say we should try tomorrow or Thursday, same time.

[Belasco_old]

Thank you Pap for your reply, when you and Brannor finalize a time and date could you please post it here.

Belasco

[Brannor]

Ikes, I forgot to add the day.

Ok, how does Thursday sound?
Same time & place

[Belasco_old]

Thank you for your reply Brannor, I shall be there Thursday.

Belasco

[Starslayer_D]

Thursday I can't, on abusiness trip. Too bad, would have liked to hear this. May I have the chat-log later on?

[Brannor]

@Star:
I will post the log here

[Brannor]

[20:11] <Brannor> *****************************************
[20:11] <Brannor> SQL-INFO-BEGIN
[20:11] <Brannor> *****************************************
[20:12] <Brannor> K. pap, please begin
[20:12] <Papillon> How are we going to do this ? Question and answer style ?
[20:13] <Brannor> Maybe just start wiht the basics.
[20:13] <Brannor> say I want to have a sql query in my script
[20:13] <Brannor> what do we need to get it going in the code.
[20:14] <w_wolf> maybe start with what's in the default tables of the nwnx db....
[20:15] <Papillon> Ok, there are two ways of using aps/nwnx
[20:16] <Papillon> The easy and the more complex way, whitch is also more powerful.
[20:16] <Papillon> The easy way makes use of the default table "pwdata" and offers simple commands for storing persistent Integers, Floats, Strings, etc.
[20:16] <Papillon> The more complex way give you everything you can do with SQL.
[20:17] <Papillon> I think we can ignore the simple functions for now, as they should be self explanatory.
[20:17] <Papillon> Any objections ?
[20:17] <Brannor> what simple functions? Just for logs sake
[20:18] <Papillon> SetPersistentInt(object, name, value) for example
[20:18] <Papillon> and it's counterpart, GetPersistentInt(object, name);
[20:18] <Brannor> oh, ok, nah, we dont need that
[20:19] <Papillon> Ok, what you need to do to send SQL queries to the database:
[20:19] <Papillon> 1. #include "aps_include" at the top of your script
[20:19] <Papillon> 2. Call SQLExecDirect(sql_string_here)
[20:20] <Papillon> 3. If you expect the query to return data (i.e. a SELECT) then you can fetch the first row of the resultset with SQLFetch();
[20:20] <Papillon> SQLFetch return SQL_SUCCESS if it could retrieve a row, SQL_ERROR otherwise.
[20:21] <Papillon> You can go through all of the rows in the result set with the following code (just an example):
[20:21] <Papillon> int rc = SQLFetch();
[20:21] <Papillon> while (rc == SQL_SUCCESS)
[20:21] <Papillon> {
[20:21] <Papillon> // do something with the row
[20:21] <Papillon> rc = SQLFetch();
[20:21] <Papillon> }
[20:21] <Papillon> 4. A row in the database consists of at least one column
[20:22] <Papillon> You can retrieve the columns with the function
[20:22] <Papillon> string SQLGetData(column_number);
[20:22] <Papillon> >> Always check for SQL_SUCCESS before using SQLGetData
[20:22] <Brannor> is that the only way? by column number?
[20:23] <Papillon> Yes, only by column number.
[20:23] <Brannor> ok
[20:23] <Papillon> This may be changed later, but honestly, I never felt the urge to specify columns with their name.
[20:23] <w_wolf> Brannor: you can define your column names as variables on top of the script if you want it to be clearer....
[20:23] <Brannor> just asking, np!
[20:23] <Brannor> It would probably come up inthe faq anyway
[20:24] <Papillon> What I usually do is a) fetching the row b) assigning all columns to script variables right after the fetch.
[20:24] <Papillon> That makes clear what is what.
[20:25] <Brannor> are there any limitations to the query's?
[20:25] <Brannor> length, complexity?
[20:26] <Papillon> Your query can return an unlimited amount of rows
[20:26] <Papillon> The total amount of data in a row is 1024 bytes at maximum.
[20:26] <Papillon> Although that limit is configureable in aps_include.
[20:28] <Brannor> k
[20:28] <w_wolf> what happens if two scripts are reading from a query at the same time? do the result sets overwrite each other?
[20:29] <Papillon> That can not happen.
[20:29] <Brannor> what about commits and rollbacks?
[20:29] <Papillon> The NWN Server is not multi threaded, so only one script can run at a time.
[20:29] <w_wolf> i remember you proposed to fake multitasking by breaking loops with many iterations into smaller chunks to avoit congestion
[20:30] <Papillon> Yes, that's true. The database support in NWNX2 supports only 1 resultset at a time, so two different scripts would overwrite the results.
[20:31] <Papillon> I usually read in the whole resultset in some data structure, and then the delay iterations run.
[20:32] <Brannor> what about commits and rollbacks?
[20:32] <Papillon> What do you mean specifically ?
[20:32] <w_wolf> mysql doesn't support that afaik
[20:32] <Brannor> that solves it all
[20:33] <Papillon> Well, you can send all the usual SQL commands through NWNX to the database.
[20:33] <Brannor> do i have to send commits and rollbacks was the question.
[20:33] <Brannor> but from what wolf say it is not
[20:33] <Papillon> So if your database supports transactions or stored procedures you can use that functionality.
[20:34] <Papillon> Right, you don't have to.
[20:34] <Brannor> K
[20:35] <Brannor> so everything I fire is done ASAP.
[20:35] <Brannor> good to know
[20:35] <Papillon> Yes, but that also means that especially complex queries, or searching for columns that aren't indexed will lag the server.
[20:35] <w_wolf> it's done before SQLExecDirect returns or is nwnx2 asyncronous?
[20:36] <Papillon> The functions in aps_include have a close relation to their ODBC counterparts.
[20:36] <Papillon> Even the names are the same.
[20:37] <Papillon> SQLExec executes the query, while SQLFetch only fetches the results.
[20:37] <Papillon> So the processing is done when SQLExecDirect is called.
[20:40] <Brannor> ok, thats getting, what about putting
[20:41] <Papillon> Nothing special here.
[20:41] <Papillon> You call SQLExecDirect with a statement like
[20:41] <Papillon> SQLExecDirect("INSERT INTO table_abc VALUE (1,2,'abc')");
[20:41] <Papillon> Again the query will be executed immediately. You don't need to call any other function.
[20:43] <Brannor> ok, cool.
[20:44] <w_wolf> do you have a return code from that?
[20:44] <Papillon> You will see an error in the nwnx.log logfile, but there's no way to tell from NWScript right now.
[20:46] <w_wolf> ok, so we have to be sure not to hit conflicting keys....
[20:47] <Papillon> Yes. But a clean script would query for existing rows before an insert, anyway.
[20:47] <w_wolf> sure, i was thinking about optimization shortcuts
[20:47] <Papillon> The persistent containers are a good example. It checks if the item is already in the database, and if it is, it just increases the "count" column.
[20:49] <w_wolf> can you send us (or put on the contractors dir) some of those scripts, like the ones used for chests, merchants and craftbles?
[20:50] <Papillon> They're included in the aps distribution.
[20:51] <w_wolf> even the crafting ones?
[20:51] <Papillon> No, the crafting code is not included.
[20:51] <Papillon> I can email you a small mod with the latest code and some crafting containers.
[20:53] <w_wolf> ok, and a dump of the structure needed to recreate the needed tables
[20:55] <Papillon> The crafting code needs only one table as it is now. Silk put the recipes and ingredients in the database too, but I never had a look at that code.
[20:56] <w_wolf> that's what we want to work on, though... so that's the code we need i think
[20:56] <Brannor> yep, but I wanted to get the dig from Pap and thought it would be interresting for all
[20:57] <w_wolf> (brannor correct me if i'm wrong of course)
[20:57] <Papillon> Ok, I'll export the current code from Elysia into the development mod then. (Elysia is where the changes were made, isn't it ?)
[20:57] <w_wolf> yes
[20:57] <w_wolf> i have the query to fill up the 2 new tables, so i only need the structure dump
[20:57] <Brannor> yep, that would be great, thanks pap
[20:58] <Papillon> ok, I'll paste the statements right here
[20:58] <w_wolf> cool.
[20:58] <w_wolf> then if you manage to do send us that test mod by friday night i can work on it during the weekend...
[20:59] <Papillon> Here they come:
[20:59] <Papillon> # Host: localhost
[20:59] <Papillon> # Database: nwn
[20:59] <Papillon> # Table: 'ars_ingredients'
[20:59] <Papillon> #
[20:59] <Papillon> CREATE TABLE `ars_ingredients` (
[20:59] <Papillon> `resref` varchar(100) NOT NULL default '',
[20:59] <Papillon> `ingredient` varchar(100) NOT NULL default '',
[20:59] <Papillon> `amount` int(11) NOT NULL default '0',
[20:59] <Papillon> PRIMARY KEY (`resref`,`ingredient`)
[20:59] <Papillon> ) TYPE=MyISAM;
[20:59] <Papillon>
[20:59] <Papillon> # Host: localhost
[20:59] <Papillon> # Database: nwn
[20:59] <Papillon> # Table: 'ars_recipes'
[20:59] <Papillon> #
[20:59] <Papillon> CREATE TABLE `ars_recipes` (
[20:59] <Papillon> `resref` varchar(100) NOT NULL default '',
[20:59] <Papillon> `name` varchar(100) NOT NULL default '',
[20:59] <Papillon> `class` varchar(100) NOT NULL default '',
[20:59] <Papillon> `skill` varchar(100) NOT NULL default '',
[20:59] <Papillon> `level` int(11) NOT NULL default '0',
[20:59] <Papillon> `base_xp` int(11) NOT NULL default '0',
[20:59] <Papillon> `sound` varchar(100) NOT NULL default '',
[20:59] <Papillon> `active` int(11) NOT NULL default '0',
[20:59] <Papillon> PRIMARY KEY (`resref`)
[20:59] <Papillon> ) TYPE=MyISAM;
[20:59] <Papillon>
[20:59] <Papillon> # Host: localhost
[20:59] <Papillon> # Database: nwn
[21:00] <Papillon> # Table: 'ars_skills'
[21:00] <Papillon> #
[21:00] <Papillon> CREATE TABLE `ars_skills` (
[21:00] <Papillon> `skill` varchar(100) NOT NULL default '',
[21:00] <Papillon> `description` varchar(100) NOT NULL default '',
[21:00] <Papillon> PRIMARY KEY (`skill`)
[21:00] <Papillon> ) TYPE=MyISAM;
[21:00] <Papillon>
[21:00] <Papillon> # Host: localhost
[21:00] <Papillon> # Database: nwn
[21:00] <Papillon> # Table: 'tradeskills'
[21:00] <Papillon> #
[21:00] <Papillon> CREATE TABLE `tradeskills` (
[21:00] <Papillon> `player` varchar(64) NOT NULL default '',
[21:00] <Papillon> `charname` varchar(64) NOT NULL default '',
[21:00] <Papillon> `skill` varchar(32) NOT NULL default '',
[21:00] <Papillon> `xp` int(11) NOT NULL default '0',
[21:00] <Papillon> `expire` int(11) NOT NULL default '0',
[21:00] <Papillon> `last` timestamp(14) NOT NULL,
[21:00] <Papillon> PRIMARY KEY (`player`,`skill`,`charname`)
[21:00] <Papillon> ) TYPE=MyISAM COMMENT='Tradeskills experience of players';
[21:00] <Papillon>
[21:00] <Papillon> (For mysql)
[21:01] <w_wolf> sure, that's what i have on my test box
[21:01] *** Durenunde (oevns@p5085FBFC.dip.t-dialin.net) has joined channel #avlis
[21:01] *** Mode change "+o Durenunde" for channel #avlis by ChanServ
[21:01] <Papillon> What code changes do you want to make ?
[21:02] <w_wolf> i think the only thing missing is the contents of ars_skills but i can easily mae them up from the content of ars_recipes
[21:02] <w_wolf> well, for now it would be good if it worked....
[21:03] <Papillon> # MySQLCC - [Avlis mySQL Server] Query Window
[21:03] <Papillon> # Connection: Avlis mySQL Server
[21:03] <Papillon> # Host: localhost
[21:03] <Papillon> # Saved: 2003-05-08 15:02:05
[21:03] <Papillon> #
[21:03] <Papillon> # Query:
[21:03] <Papillon> # SELECT *
[21:03] <Papillon> # FROM `ars_skills`
[21:03] <Papillon> #
[21:03] <Papillon> 'skill','description'
[21:03] <Papillon> 'Blacksmithing','working with metal'
[21:03] <Papillon> 'Carpentry','working with wood'
[21:03] <Papillon> 'Tailoring','sewing'
[21:03] <Papillon> 'Weaponcrafting','the art of making weapons'
[21:03] <Papillon> 'Armorcrafting','the art of making armor'
[21:03] <Papillon> 'Alchemy','the art of creating potions'
[21:03] <Papillon> 'Brewing','the art of brewing beverages'
[21:03] <Papillon> 'Herbalism','the art of creating herbal items'
[21:03] <Papillon> 'Jewelcrafting','the art of creating jewelry'
[21:04] <w_wolf> thanks
[21:04] <Papillon> What does not work ? I'm totally uninformed about the tradeskills right now
[21:05] <w_wolf> i made only some basics tests and the only recipe working is ore to ingots....
[21:06] <Papillon> oh ok. I thought Silk would have sorted that out.
[21:06] <Papillon> Btw, if you're a total genious, you can redo the whole system.
[21:06] <Papillon> The way the database for the tradeskills works now is that after the module started, all of the recipes are read into the database.
[21:07] <Papillon> So basically, there's no change to the actual crafting code itself, only ars_recipes has been changed to ready everything from the DB.
[21:07] <Papillon> Now it would be brilliant to have a database design and corresponding code that puts the burden of finding out what you can craft with the current ingredients to the database server.
[21:08] <Brannor> that is exactly what I want to do
[21:08] <Papillon> Unfortunately, I fear there is no database design and SQL commands that can do that.
[21:08] <w_wolf> this is doable, but it might be too much a burden on the server.... i have to see the code to say if it can be put as a priority thing or not.
[21:09] <w_wolf> well, we can't make it all with SQL, some code will be needed server-side, but it's doable.
[21:09] <Papillon> The basic problem of finding out the recipes that can be crafted is almost impossible to do. At least me and a friend didn't come to a solution.
[21:10] <Papillon> You would have to do much processing in nwscript, and then you wouldn't really win anything
[21:10] <Brannor> doable, probably, with low cycles, never
[21:11] <w_wolf> well, i think the priorities would be:
[21:11] <Papillon> I was shooting for a single SQL statement that returns all matches in a resultset... but no dice.
[21:11] <w_wolf> 1) debug the current code so that the latest version of the recipes can be made completely
[21:12] <w_wolf> 2) change it so that the recipes can be re-read runtime (at specified intervals or manually with a new function of the DM book of skills) so that we don't need to reset a module to get new recipes in
[21:13] <w_wolf> 3) make some tests of this "recipe AI", first offline then live on a low- traffice server
[21:13] <Papillon> That would be a good option for the DM book of tradeskills.
[21:13] <w_wolf> like le'or
[21:13] <Brannor> what I was thinking was to hardset the crafting dialog.
[21:13] <Brannor> so you have all doable stuff in the dialog, but you willhave to browse to the recipe needed.
[21:13] <Papillon> It is like that now.
[21:14] <w_wolf> (i'm afraid i have to leave in a while, anyway i'll keep irc open to get the logs)
[21:14] <Brannor> but in a branch system
[21:14] <Brannor> kinda like in the toolset
[21:14] <Papillon> I'll work on some improvements to the gameplay over the next couple of days.
[21:14] <Papillon> e.g. mining bags and the like.
[21:14] <w_wolf> cool
[21:15] <Brannor> those would be good.
[21:15] <Brannor> finally shut chasman up
[21:15] <w_wolf> brannor, what you propose can be done by extending the db, i can work on it as a priority 3 and move the other thing down
[21:15] <w_wolf> quite easy anyway, we just need one more table with "recipe categories"
[21:16] <Papillon> Not needed.
[21:16] <Papillon> The skills are the categories.
[21:16] <Brannor> the dialog?
[21:16] <Brannor> if so then great.
[21:16] <Brannor> I want a tree system in there.
[21:16] <Papillon> An Anvil won't offer you recipes for brewing.
[21:16] <w_wolf> uhm, if i understand brannor he wants a thing like: open the anfil, he asks you: blades, axes, blunts, long weapons
[21:16] <Brannor> exactly
[21:17] <w_wolf> click blades you get short, medum, long
[21:17] <Brannor> weapons, armors
[21:17] <Papillon> Ok, so more like subcategories. I get it now.
[21:17] <Brannor> 100%
[21:17] <Brannor> a lot *neater*
[21:17] <w_wolf> sure, anything you like, just tell me the tree depth and i can work it out in a short time, if you want an infinite tree it can be done still but it's a bit heavier on the server as we need to do recursivequeries to rebuilt that
[21:18] <Brannor> and the leafs could be hidden if not craftable?
[21:18] <w_wolf> not craftable with the current ingredients, current skill or what?
[21:18] <Brannor> skill, everything else is to much
[21:18] <Papillon> Hiding the leafs is super heavy on the server. That's why I removed that feature again.
[21:18] <w_wolf> ok, no big deal.
[21:19] <w_wolf> really?
[21:19] <Papillon> Althought I was hiding based on the current ingredients.
[21:19] <w_wolf> is that much of a buden to rebuild the menu each time?
[21:19] <w_wolf> no, i agree that on ingredients is heavy, but level is just a compare....
[21:19] <Papillon> No, but the processing what should be shown and what not is heavy. At least when you have > 50 recipes to choose from.
[21:20] <Papillon> That is already in the code (the challenge rating).
[21:20] <w_wolf> ah, right, i remember once i tried with my alt, she had less things on the menu....
[21:21] <w_wolf> moving that to the tree view and hiding empty subtrees is easy then
[21:21] <Papillon> The only thing in the code that might be hard to understand are the hashsets.
[21:21] <Brannor> why not all at once?
[21:21] <Papillon> But you will get the hang of it.
[21:21] <w_wolf> my first programming language is currently Perl, so i don't think it wwill be a problem
[21:22] <Papillon> the programmatic eclectic rubbish language ?
[21:22] <w_wolf> that one
[21:23] <Brannor> hehe
[21:23] <w_wolf> and my job often means also debugging other people's code in perl.
[21:23] <w_wolf> so debugging nwscript cannot really scare me
[21:23] <Brannor> the wost thing in any language
[21:23] <Papillon> Uhm.. let's talk again later
[21:24] <Papillon> But I hope you will find the code easy to understand. I tried to keep it clean.
[21:24] <Papillon> Although you will see much code that is commented out.
[21:24] <Papillon> brb
[21:25] <Brannor> wolf, will you check out the dialog-tree thing?
[21:25] <w_wolf> sure, i can move it to priority 2
[21:26] <w_wolf> now i really have to go though, i'll check the logs later and mail you comments/questions...
[21:26] <w_wolf> thanks pap for now
[21:26] * w_wolf wawes
[21:26] <Brannor> not overly important but it would probably help the people.
[21:26] <Brannor> even if it allways has everything included
[21:26] * w_wolf waves, too
[21:26] <Brannor> ok, cu!!!!
[21:27] <Brannor> thanks for stoping by
[21:27] <Papillon> cu! I'll be gone for dinner now
[21:27] <Drache> bye pap
[21:27] <Drache> thanks
[21:27] <Brannor> ok, thanks for the into pap
[21:27] <Brannor> I'll post the log later
[21:28] <Brannor> *****************************************
[21:28] <Brannor> SQL-INFO-END
[21:28] <Brannor> *****************************************