Demo module

[Grinning Fool]

Has anyone else had issues accessing the MySQL plugin demo from within the main module? i.e., does the portal appear? and if so, does it correctly load?
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[caloup]

Hello !

I'm trying your last demo mule and when i go to the MySQL portal the NPC tell me that all work fine...

I've pulled the first lever and enterig : lalali la la je m'amuse bien , ; : ! ù ^& é " ' (...

A message tell me that it's ok my words are stored. When i pull the second lever i've got this message : "NWNX!SGL!GET_ESCAPE_STRING"

if i put to the first lever "fsdfqsfqsfqfqsfq" the second lever work and give me what i pull to the first lever...

For the chest...you should give to PC an object because i went in the module with a new PC and have not an object to store...

[caloup]

it's strange.... i retry to put something like this :

" la la la , ; : ! ù ^= ) à ç _ è - ( ' " é & "
And i haven't got any mistake...... ???
I don't understand !

Great job grinning !

[Grinning Fool]

Hmm, thanks; I'm removing the 'store object' chest, to eliminate confusion since it's not supported by the plugins yet. I hadn't tried saving values with extended characters, so I'll have to do a bit more testing around that.
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Grinning Fool]

Sooo far behind on everything. Updates this weekend. really this weekend, this time
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Cord]

Grinning Fool, thanks for the help getting my NWNX4 installed it is working now.
_________________
Age of Agress

[Asmodae]

I've noticed that there are some differences in the MySQL vs SQlite demo module build tables functions. (this is the latest nwnx release v1.07)

the main difference is that for SQLite, the pwobject table uses text data type for the main data instead of blob. Is there a reason for this? I know also, some keywords in the SQLite table are capatilized vs the MySQL table. Any particular reason for this?

I know in general its not a big deal, but we're looking at having multiple developers, and I'd like to keep my SQL portable so that only the primary NWN2 server only has to run a full MySQL installation and developers can stay with smaller sqlite without losing anything. (we currently have no incliniation to use stored procs )
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[Grinning Fool]

[Papillon]

The table structure should be the same across all modules. Since the demo modules will be used by most newcomers to create their initial DB structures, please make sure the demo mods are consistent, if possible.
_________________
Papillon

[Gryphyn]

[Grumalg]

Not having gone back to look, my memory says BLOB was only used in the pwobjdata table where there's no other viable choice.

From a MySQL perspective there are good reasons to avoid the use of TEXT and BLOB datatypes unless you have no choice. To understand why you have to look at how MySQL stores data.

A row in a table is stored as contiguous bytes to a max size of 65,532 bytes. Since TEXT and BLOB fields can contain data larger than this they are not stored within the row data, but at a different place in the DB file indicated by such a column's bytes within the actual row data. This means TEXT and BLOB columns always require additional HD head seeks to another location in the DB file to obtain that columns data. Thus, TEXT and BLOB carry a performance penalty.

Unless you're storing something huge VARCHAR is a better choice than TEXT. Up to MySQL 5.0.2 VARCHAR was limited to 255 chars, but in MySQL 5.0.3 and later VARCHAR can be up to 65,535 chars. You do have to watch out that the total row size of all columns (and their overhead bytes) doesn't exceed the 65,532 max size of a row though. It's kinda strange that they define a VARCHAR as being capable of holding more than a row can, but it's probably a compatability thing with SQL standards.

--- Grumalg ---

[Grinning Fool]

Varchar(62000) or so it shall be, if nobody has objections
_________________
Khalidine, a NWN2 persistent world

Looking for volunteers.

[Grumalg]

I wasn't trying to force the choice to be VARCHAR, just bringing up factors that should be considered in making the choice. Not having dug in SQLite, I can't speak to how it's datatypes are stored nor whether they have the same behaviour as MySQL's datatypes.

Useing VARCHAR's larger than 255 chars *will* break the ability to support any MySQL version earlier than 5.0.3.

The use of large VARCHAR's also has implications in how big the sSpacer size in nwn_sql would have to be to read them back, but that's true of any large piece of data being read back, including TEXT and BLOB.

VARCHAR does have some very nice features though, besides the fact that it's stored within the row data in MySQL. To understand why you again have to look at how things are stored in MySQL.

The CHAR datatype is stored as a fixed size and if the actual data is smaller it gets right padded with the space char to fill the entire fixed size. The right space padding is stripped off before the data is returned, which can be a problem if you are relying on storing some space chars at the end of a CHAR field. Making a CHAR field large enough to fit rare cases of the data actually being that big is wasteful and bloats the DB file size. Fields where all the data is the same size is a good use of CHAR.

The VARCHAR datatype stores data in a 'p-string' style. I.E. a length value followed by the number of bytes the length indicates. For VARCHAR up to 255 in size the length overhead is one byte, and larger than 255 chars takes 2 bytes of length overhead. But the number of bytes used to actually store the data is the actual length of the data, not the defined max size. In other words VARCHAR only takes up length byte(s) + actual data size in the DB file no matter how large the field is defined as. This makes VARCHAR very efficient for cases where the data size can vary widely and forgiving if you're not certain what the data size will be in all cases as you can just make it big enough to be sure of handling the worst case without wasteing space in the DB file. The additional overhead of the length byte(s) make VARCHAR a poor choice if the field size is small and the data is mostly the same size.

Hopefully there's a SQLite guru out there who can chime in on what SQLite does with datatypes. Since the table definitions should be standardized and compromises may need to be made to support both.

--- Grumalg ---

[Gryphyn]

Most databases have a cap on a SYSNAME object - eg a table or column name - this is usually 256 bytes [255 char -utf8, 128 char -utf16/ucs2]

As a starting point this should be a minimum for NWNX.

What should the maximum be? Well the is really database dependent. But in most (the latest versions anyway) they use a 32bit length header [~ 65,532 bytes]. - This is also true for the older TEXT datatypes.

As Grumalg mentions you can take a performance hit (I/O) if you don't get your row size fitting into your page size (you databases data buffer). How this is done is managed by the database engine - so you'll have to read up on how it will effect you.

An earlier post by Papillon mentions doing away with the NWNX 'spacer' (buffer) so hopefully this becomes a dead issue.

Currently the 'spacer' is the determining factor. Make it big enough for your largest value, but then you have allocated but unused memory. mind you what's 16Kb out of 1Gb? not a lot. but when your only using, say 64 bytes out of 16Kb, there is a high percentage of seldom used memory.

Cheers
Gryphyn

[Gryphyn]