nwn database help - basic select statement

[Legacy_Calgacus]

               Hi,
I want to fetch a bunch of strings relating one of several puzzles, however I want many puzzles stored in the same database.  In sql I would do something like this:
select strName from puzzletable where puzzleid = 23;
in NWN database I can use a " where puzzleid = 23 " clause so how can I select a particular puzzle?  The puzzles get entered into the database manualy so I won't be storing any player info with them so each will just have player=OBJECT_INVALID stored instead.

Or will I just need to store each puzzle in its own database?

PS: with the GetCampaignString(  string sCampaignName,
    string sVarName,
    object oPlayer = OBJECT_INVALID ) function, does it treat sVarName as a column name or as a key and some other field as the value?
Here is what my isitial attemp at making a database file looks like, how could I fetch the value of column intr1 if I had ultiple rows?   or even if I had just one row?
http://calgacus.game...atabasefile.bmp
               
               

               

                     Modifié par Calgacus, 08 novembre 2010 - 01:19 .
                     
                  

            

[Legacy_Lightfoot8]

               You will just need to make a unique name for each string you want to place in the DB and use that name to retrive the string.



So if puzzle 1 had say 5 strings in it you could call them.



Puzzle1_Str1

Puzzle1_Str2

Puzzle1_Str3

Puzzle1_Str4

Puzzle1_Str5



If you have a differant number of strings in each puzzle you may want to also give a entry into the data base for that.  Puzzle1_Entrys or smmething.  



then getting your data out of the DB you would just need to build the name for the entry you want to retrive.  



string sEntryName = "Puzzle"+IntToString(nPuzzleNumber)+"_Str"+IntToString(nStringNumber);

string sPuzzleString = GetCampaignString( sBDName, sEntryName);



Hope that helps.



               
               

               
            

[Legacy_FunkySwerve]

               You do know you can use SQL if you're building for a PW, right? Just use NWNX with ODBC:

http://www.nwnx.org/



Nwn's native database is a joke when it comes to stuff like this.



Funky
               
               

               
            

[Legacy_Calgacus]

               Thanks Lightfoot8,

So the sVarName parameter then refers to a value in a column inside the DBF file?   Since I am making these files manually what column names should I give my fields?



BTW, what about those  CDX files, how who and when do they get generated?
               
               

               
            

[Legacy_Calgacus]

               Funky,

Yes but I am modding for nwn single player now, and i am looking forward to dragon age modding just for this reason.
               
               

               
            

[Legacy_Lightfoot8]

               Either I didn't  understand  the question or you did not understand the answer. I am not really sure wich.
The nwn DB does not have any collumns.  It is a simple VarName = value.  All entrys in the DB have to have a unique VarName.  You can simulate collumns by the method I showed above, But they are really just a bunch of single entrys. 

As far as when a CDX file gets created.  It is created along with two other files(.ftp and .dbf)  to make up the NWN DB by the game.  All three files make up the single DB.  

  Since it is starting to sound to me like you are trying to create the DB outside of the game. I can honstly say I have no Idea how to do that.  I have only looked at the file formats once a long time ago and never spent enough time to figure them out.

I can say that what you are trying to do is going to be a pain in the *bleep* to try and do with the nwn DB.  fixing bugs or spelling would be more work then it would be worth. At least by the ways I would know how to go about it.  
A second option would be to just create a 2da file for your data.  not knowing the length of the strings you plan on creating or the content of the strings( would they contain carrige returns?), I do not know if this would be a good option or not. 

A 2da would be simple if the strings where not two complex.

2DA V1.0

     nString     String1     String2     String3     String4  String5
0     3       "string1"   "this is string 2"  "and string 3"   ****  ****
1      5      " This would "  "Be puzzle 1 "    " Or the second puzzle"   " in the 2da with 5 strings"   "Puzzle 0 had 3 strings" 

If needed You can find the 2da file format at the link below.   Since they are plain text files they are easy to create and edit.

Documentation: 2DA File Format
               
               

               

                     Modifié par Lightfoot8, 08 novembre 2010 - 03:11 .
                     
                  

            

[Legacy_GhostOfGod]

               

Calgacus wrote...
I want to fetch a bunch of strings relating one of several puzzles, however I want many puzzles stored in the same database

Normally one might use the NWN database in this particular situation with one database per puzzle with all the different strings stored under that one database name.

SetCampaignString(string sCampaignName, string sVarName, string sString);

The first parameter would be the Database name(CampaignName).
The second parameter would be the ID of the string we are going to strore.
And the third parameter would be the actual string itself that we stored.

To retrieve the string you you look in that Database for the particular ID and get the string.

There is one way of doing it so that you could store many strings under one ID but it requires a bunch of string parsing with scripting. It can be a bit of a pain in the you know what and in game can cause some lag. If you are intent on doing this with the NWN database I could post some examples of how you could use string parsing to do what you need.

Lightfoot offers an excellent alternative as well.

If you are already familiar with sql then perhaps you may want to go with Funky's suggestion and look into using NWNX. It is much faster and might be better for your needs.

Good luck.
               
               

               

                     Modifié par GhostOfGod, 08 novembre 2010 - 03:47 .
                     
                  

            

[Legacy_Calgacus]

               Thanks,
I think I figured it out,  the nwndb uses a standard table template like this http://calgacus.game...ardtemplate.bmp, if you ask for an int it returns the value in the "int" column, if you ask for a string it returns the value in the "memo" column, it uses the "varname" column values to match with the sVarName param and the playerid column values to match with the PC.

To have more than one puzzle in the same table I need to use Lighfoot8's suggestion.  probably just as easy and quick to use one table per puzzle but either seems easy now.

esentially a standard cal to
GetCampaignString(string sCampaignName, string sVarName, string sString, object oPC);  translates to a select like so:
select memo  from sCampaignName where varname == sVarName   and playerid == oPC;

***** So I cannot add my own columns to the database. :-(  *****
also
GetCampaignInt(string sCampaignName, string sVarName, string sString, object oPC);  translates to a select like so:
select int  from sCampaignName where varname == sVarName   and playerid == oPC;

PS: I was thinking of 2DA files but my strings are kinda long - maybe 1000 characters, and I also I want people to just be able to download and use new databases full of fresh puzzles without having to get a new copy of the mod file.  I know  I could  give them 2das they could drop in their override folder so thats still an option.  

So what do you think is a better option, I guess it might be easier to work wit the 2DAs.  
How will the read performance compare? 
The database files I will at least be able to update ingame - say to mark a given puzzle as used - so it won't get reused the next time someone plays the mod or to update  a puzzle counter to indicate what puzzle to use next. 
Will my override folder 2DAs take effect if dropped in after a mod has been loaded and a game saved?


Thanks
               
               

               

                     Modifié par Calgacus, 08 novembre 2010 - 04:15 .
                     
                  

            

[Legacy_Calgacus]

               of course I could use both, a 2DA to store the puzzles, a tiny DB to store info about which puzzle was used, which to use next, etc
So I could always add puzzles to a 2DA, people could just drop it into the override and the DB would still be valid and no user would reuse a puzzle after refreshing the 2DA.
When do 2DA files get loaded?  on module load?  only when called on for a value?
               
               

               

                     Modifié par Calgacus, 08 novembre 2010 - 04:29 .
                     
                  

            

[Legacy_FunkySwerve]

               Do the puzzle states need to be persistent across saves, or are you just trying to store their data in a convenient way? If the former, I'd go with locals. If the latter, I'd simply store the data in script. NWN DB calls are EXTREMELY slow, though reads are quite a bit faster than writes.



Oh, just read your post again - getting late here, sorry. 2da performance will FAR exceed any other option, including coding into script, since the new caching was put in. Just don't use more than say, 5-8 2das for your puzzles. Remember, the new caching defaults to 10 cached 2das, and you may occasionally want to pull up another. If you read them out of caching, they are slow as molasses compared to anything BUT the NWN DB (which puts molasses and snails to shame, especially on writes - actually, exaggeration aside, its read speeds might exceed uncached 2das, but that discussion is largely academic).



The string length problem is solved by using StrRefs to tlk table entries - you would use a custom tlk to go with your 2da.



I don't know how 2das will take to being hot-dropped into a loaded game. I don't work much in SP.



You WOULD need Persistants in order to track data across games. I would suggest only using them for tracking which puzzles have been done, and I would suggest a flagset to keep reads to a minimum.



Funky
               
               

               
            

[Legacy_FunkySwerve]

               I see you twigged to the same notion in your latest post. With a flagset you can store up to 32 different binary puzzle states (done/not done) if you don't have an ordering. If you do have a fixed ordering (ie puzzle 1 done, puzzle 2 is next, then puzzle 3 when 2 is done), you can store over 2 billion, no flagset needed.

Funky
               
               

               

                     Modifié par FunkySwerve, 08 novembre 2010 - 04:39 .
                     
                  

            

[Legacy_Calgacus]

               How big can a 2DA string be?  If I use a custom tlk, can the users drop it into the override?
               
               

               

                     Modifié par Calgacus, 08 novembre 2010 - 04:54 .
                     
                  

            

[Legacy_FunkySwerve]

               Short answer: no idea.



Longer answer: it doesn't matter, put it in a tlk. '>



What are you looking to store? I know you mentioned 1k lines, which is going to be VERY inconvenient when trying to do column spacing - nevermind the spaces between words that would have to be parsed in and out.



Funky
               
               

               
            

[Legacy_Lightfoot8]

               One thing to keep in mind is that the PlayerId is equal to the characters name.  This can become unreliable if two players have characters with the same name.  



VarType will determine the collumn that is retrived by the call with GetCampaign?  with GetCampaingnstring returning the what you have as the memo collumn(assumeing that you didn't add the memo collumn yourself).  Since the vartype is hard coded in the game, I don't see where you will be able to add collumns to the DB without hak into the game.    



If I am off base here and you can get a VarName to hold more then one data type and get the game to read it durning play, I would sure like to hear about it.
               
               

               
            

[Legacy_FunkySwerve]

               

If I am off base here and you can get a VarName to hold more then one data type and get the game to read it durning play, I would sure like to hear about it.

Of course you can. Just concatenate, with a separator, in string format. I used to do this quite a bit before I swapped to NWNX.

Funky