SQLExecDirect() within while(SQLFetch())

[weldieran]

OK - So i've now come across two different situations where I would need to do something like this

[dguntner]

That looks a bit convoluted. What exactly is it that you're trying to do? And can you show what your table layout is for "thistable?"

--Dave

[weldieran]

I know there are ways around what I want to do.. i went around the first time i ran into this...

but running into it again makes me think about modifying nwnx so that it can handle more than one query at a time.

the first scenario, which i've already worked around, was a custom item storage system. If you put a bag into the storage system, it marked the item as "in a bag" then uses the bags "id number" so that when it loads the items back up from database, it puts stuff in the rightplace.

The first query checks for all "noninside" bag items
then inside that query, when it comes across a bag, it would want to run the query that loads the items for that bag.


ya sure, more ways to do any one thing - but in both these situations i've come across, it would've been best to be able to run another query without wiping out yer first query.

[dguntner]

That's not quite what I meant. The way you're doing your sample queries there looks kinda like you'd be looking something up in one table and then comparing that against something in a second table, but your example only references one (the same) table. That's why I was asking for your table structure, so that I can try to grasp better what it is you're trying to do.

--Dave

[Papillon]

Many times you should be able to use a join instead of nested loops, which are more efficient as well.
_________________
Papillon

[weldieran]

autoid | userid | playerid | resref | identified | stacked | container_id | in_container_id | resourcecount | pickpocketable | stolen

There's the table.

Now - this is for persistant storage and utilizes my custom player persistence system (thus the userid, playerid).

When storing the items, and it comes across a bag, it marks the bag with an ID number (1st bag, 2nd bag, 3rd bag, etc). Then when it comes across the items inside that bag, it stores the item with the appropriate ID number of the bag. All works great.

When querying for the data - i have to do all kinds of funky moves to work around the fact that nwnx/aps doesn't support more than 1 query at a time.

What I would've LIKED to do is:

[monezz]

I defined a test table called recur to simulate the problem.

mysql> select * from recur order by ifnull(con_id, in_con_id) asc, con_id desc;
+----+--------+-----------+
| id | con_id | in_con_id |
+----+--------+-----------+
| 1 | NULL | NULL |
| 2 | NULL | NULL |
| 3 | NULL | NULL |
| 4 | 1 | NULL |
| 14 | NULL | 1 |
| 9 | NULL | 1 |
| 8 | NULL | 1 |
| 7 | NULL | 1 |
| 6 | NULL | 1 |
| 5 | NULL | 1 |
| 15 | NULL | 1 |
| 10 | 2 | NULL |
| 11 | NULL | 2 |
| 12 | NULL | 2 |
| 13 | NULL | 2 |
+----+--------+-----------+

pseudo code for processing:
id is resref for ease..

[Asmodae]

This should really be two, or most likely three different tables. Then sorting out which is where would be trivial. Since the post is old and the poster hasn't checked in a while I won't get into it, but if you're interested I'd be happy to detail the process for you.

-Asmodae
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[weldieran]

In the end, I've just modified the nwnx plugin to allow "channels" of data. Then modified aps to pass the "channel" parameter and wha-la!

But then it just ends up being too many instructions to reload a person's storage as there are quite a few many things I tried to track on items being put in... and all those things need checked and readded to the item coming out of storage *sigh* TMI's, i hate em.

[Asmodae]

Yeah, TMI's are annoying, especially when you think you shouldn't be getting them, or you are willing to take the performance hit for that function...

But in the end they keep NWServer running and generally keep people from writing code that soaks too much cpu time.

btw: if you construct your tables and queries properly not only can you completely avoid TMI's in cases like this, but make the process orders of magnitude faster.
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[weldieran]

ya, i'm no expert on database design... i've designed plenty, but in no way would I claim my way is right and someone elses is wrong, hehehe. I'm just a code cruncher.

Wanna explain your basic theory on DB design?

[Asmodae]

In doing research for some of my more complex system design I had to learn a lot about how databases work, or at least a bit of theory about how to design them. I spent time on a lot of different tutorials puzzling things out, but here's a few key concepts.

Concept 1: A table should describe a type of entity or object completely, preferably only one type. Like a class in OOP. A row in a table is a description of a specific instance of that type.

Say you want a characters table with rows:
id | name | playersname | lastlogin

where the id column is your primary key.

Concept 2: Data Normilization, I don't recall enough off the top of my head to put this down coherently but it involves making sure the design of your tables follows some guidelines to ensure that the relations between your data are properly modeled. Therefore your queries can return the data you need when you need it, without fancy lookup tricks.

Google "SQL tutorial" for some tips on queries. Will post some links and an example in a bit.
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[Asmodae]

I'm having some difficulty coming up with good examples based on your posts (let me know what your data and tables look like if you want more specifics), so I'll give you some from my experience.

We needed a table for character stuff, which is much like above, a table for player data, actual player info based on public cd key, and some quest data.

The first two tables are easy, here are simplified versions.

table: playerdata
playerid | cdkey | lastlogin

table:chardata
id | playerid | name | lastlocation | lasthp


The quest table was a bit trickier. The thing is, one player can have many different quests, and one quest can be done by many different players. This is a many-to-many relationship and to model properly requires and intermediary table. So we had the following table for information about the quest.

table quests:
id | questname | questnpc | description | stage1 | stage2 | stage3


Up untill now each table describes its own type of data entit, each one has an autogenerated unique id field. Now we need a table that tells us which characters have done which quests.

table questprogress:
charid | questid | complete1 | complete2 | complete3

The key here is that this table doesn't have an autogenerated unique id field. The primary key of this table is the combination of the first two fields charid and questid. Each row is uniquely described by these two fields together. In essence this is what makes up a 'questprogress' entity. The character that is doing the quest, and the quest the character is doing. The extra fields hold some data about whether each stage of the quest is complete. But my script looks back to the actual quest table if it needs to know ABOUT the quest itself. Each stage column in the quests table has a description of that stage. (again our real system is much more complex).

Now we need to get some data from our tables. To find out if stage 1 is complete for a quest:

SELECT complete1 FROM quests,chardata,questprogress WHERE quests.id = questprogress.questid AND charadata.id = questprogress.charid AND quests.id = <id of quest> AND chardata.id = <id of character>

The tables are joined on their respective id's, and we select from that resulting joined data set, the quest id and character id we want. This SELECT statement should return a single record.

The key to making this work is the 'linking' table that uses the combined id's. This saved me from having to create looping and searching code in my database. The data in each table is very much part of that object or entity, which helps make sure that we can write queries for anything we want without special code. If this doesn't apply to you, let me know what your tables and data actually are so I can provide a few more specific tips.

Lastly, I am by no means an SQL guru having picked up everything I know from looking at other people's code and web tutorials. If I've said anything grossly inaccurate (or even slightly) someone please correct me.
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[Asmodae]

heh, going through the posts I found the one where you present your table structure. You stated it is this:
autoid | userid | playerid | resref | identified | stacked | container_id | in_container_id | resourcecount | pickpocketable | stolen

The first thing I see is that you should probably have a seperate table for item data, although I don't think that would solve your problem, you would basically come to the simplified table that monezz posted, and his code looks like it would work for that.

As for TMI's, its hard to see everthing that's being done in your code for items. But if you use SCO/RCO you shouldn't need to track anything as I believe the object is saved outright, including variables, settings, flags, etc.
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[weldieran]

unfortunately, to my knowledge, linux version of nwnX and its plugins dont contain the ability to actually store objects. And there's no way I would ever run windows as the server (yes, i'm a M$ hater, hehe)

So ya, gotta track the flags and variables and recreate the objects from the resref's. It aint perfect, and i dont much like it, but i've not even a clue how to modify the DB plugin to store objects - that much is beyond me

I wish there was as much support/development for the linux version as there is for the windows version but i'm still very grateful there are the basics available for linux