best format for a huge "database"

[mgentry]

I'd like to create a macro that generates a list of spells known by a token, with each spell name a link. When you click on the name of a spell, it opens up a window displaying the complete description of that spell. I want to create a central "database" of spell names and descriptions, so that the name-clicking macro can simply pass the name of the spell to the central database, look up the corresponding description, and then display it.

My question is, what would be the most efficient way to store the central database, speed- and memory-wise? We're talking about over 1,000 spells, roughly. Should I create a library token with > 1,000 properties, one for each spell? Or should I store it in the macro code itself as like one HUGE json array full of StrPropLists?

Also, if I'm storing the spells as StrPropLists, would it be faster or more memory-economical to break each spell into a bunch of variables for each aspect of the spell, like:

Code: Select all

spellName=Magic Missile ; spellSchool=evocation; spellLevel=1; spellCasting=1; spellComponents=VS; spellRange="medium"; etc...

or would it be better to store the whole description as a single string, and then use regex to pull out various bits of information as I need them, like:

Code: Select all

spellName="Magic Missile" ; spellDescription="School evocation [force]; Level magus 1, sorcerer/wizard 1 Casting Time 1 standard action Components V, S Range medium (100 ft. + 10 ft./level) Targets up to five creatures, no two of which can be more than 15 ft. apart Duration instantaneous Saving Throw none; Spell Resistance yes" etc...

Again, this is assuming literally hundreds of entries. Any advice appreciated.

[Bone White]

Wolph42 did some research into this, and he came to the conclusion that

Faster -> Slower

StrPropLists -> JSON Arrays -> JSON Objects.

I'm pretty sure that String lists are slightly faster than string prop lists, but only marginally so. I have no idea where regex searching of a string fits into this, though I assume that the search functions for strings, string lists, string property lists and jsons are all done with regex anyway, then it'd be no faster than each own's method.

If you're worrying about nesting str prop lists inside each other, it is perfectly possible.

[mgentry]

I'm mainly worried about the feasibility of stuffing all that data into a single variable. I'm envisioning something like:

Code: Select all

spellMasterList = "spellName=Fireball; spellDesc=blahblahblah, spellName=Ice Storm; spellDesc=blahblahblah; etc.

...where each "blahblahblah" is 2-3 paragraphs of text, and there are over a thousand entries. Is there any length limit? And would the macro take forever to load every time I call it?

[wolph42]

If its static data then I would suggest to store it in a table and store a key list On a lib token (tables only work with numbers so you need a translation table eg fireball=122, bolt = 30 etc. also store the it as a strprolist in the table. And I would advise you to use my excel to table tool. Link in sig.

btw, I noticed that Aliasmask mentioned something of picking up on his spell library again. If thats the case then I would pm him to see whether you can be to each others advantage (and at the same time prevent doing the same thing twice).

as an alternative you could check out my framework and have a look at the lib:compendium. What I do is create 1 property with a huge json object containing all data. About 1000+ entries with about 30+ fields. I build this json object by code and I build the code with the use of an xls trick. The json object is extremely slow to process (generating a form containing all data takes 2-5 minutes) so I devised a caching method (create the form once and store it in a lib property and retrieve when needed) getting the same form up using the cache method takes less then a second.
The main reason I choose this path is because the data is in principle static, but in reality it changes constantly (e.g. typos, errata, new entries, update of the code, new features, etc) so I want to have a method that I can easily update (excel) and easily transfer the updated data and process (copy/paste code and run the json creation code and caching macro once).

[aliasmask]

I use a method of related property names to handle nesting of data. So, one spell has one property. Property String lists are fast and convenient to use when defining local variables en mass using Wiki: varsFromStrProp(), but are limited in the format and characters it can use without other manipulation and conversion. That's why I tend to use one dimensional json objects. Also, jsons have many more useful tools to help manipulate the data and speed of use is virtually the same with 300 items or less. One problem with speed is passing the data to User Defined Functions (UDFs) because it starts out as a json array and then when passing an object it creates a nested json. It's not that bad unless you are already passing a nested json, or your json is large, or making that function call 100s of times. It's possible to pass the variable without passing it if you pass it by reference where the sub-macro uses the same variable scope as the caller and instead of passing the structure you pass the name.

With huge databases, the difficult part is not so much getting the data in to MapTool, but what you do with it afterwards. What you really don't want to do is grab your full data set and manipulate it. So, I create indexes for related data. For spells, I have a list for all wizard spells, all strength domain spells and all spells with darkness descriptor. Instead of storing the data in these lists I just store the property name. When loading the data on to token, I use a parser that extracts the data and adds them to the appropriate lists.

The new thing I'll be doing this time is instead of pulling data from a static table, I'll be storing it on the token. I'll also be separating the data by source and by category which will be the main "key" values (those who use sql will know what I mean) as well as a cleaned version of the name to make it variable friendly (no spaces or special characters). So, given those three keys I can retrieve any data fairly fast. So, if I know the data I want is a spell from the PHB and it's called Acid Arrow, then I do this:

Code: Select all

[H: data = getLibProperty("phb.spell.acidarrow")]

<!-- OR -->

[H: data = getLibProperty(strformat("%{source}.%{cat}.%{name}"))]

[wolph42]

what's the advantage of that?
Are there e.g. multiple acidarrows? or are you also able to get e.g. all 'phb.spell' items or all 'phb' or all 'spell' lists? And if so...how?

I imagine that the property "phb.spell.acidarrow" contains e.g. a strprop with all the relavant values, so how would you then get all property names starting with or containing 'phb.'?

[JML]

…or are you also able to get e.g. all 'phb.spell' items or all 'phb' or all 'spell' lists? And if so...how?

I just stumbled on this: Wiki: getMatchingProperties()

[aliasmask]

what's the advantage of that?
Are there e.g. multiple acidarrows? or are you also able to get e.g. all 'phb.spell' items or all 'phb' or all 'spell' lists? And if so...how?

I imagine that the property "phb.spell.acidarrow" contains e.g. a strprop with all the relavant values, so how would you then get all property names starting with or containing 'phb.'?

Actually with 3.5 and Pathfinder, yes, there are multiple acid arrows. Yes, I can get all phb or spell using indexes but I could use getMatchingLibProperties if I didn't use indexes.

I still haven't decided yet, and can always change my mind later but I may end up using multiple lib tokens. But for now, everything will be on one token.

I do plan to have a form and editing power, but I won't be editing more than one thing at a time, so I don't need unique names for properties. But if I did, I could use the property name plus the key name to create it like "phb.spell.acidarrow.name".

phb.spell.acidarrow will actually be a json because my data will have ;'s in it, html and perhaps macro code in the future to hold formulas for calculated values. I could use macro code and json.evaluate to quickly define variables locally if I formatted the data like that.

The real ugly part comes when I do the indexes because those will have to use the unique naming system.

[wolph42]

…or are you also able to get e.g. all 'phb.spell' items or all 'phb' or all 'spell' lists? And if so...how?

I just stumbled on this: Wiki: getMatchingProperties()

ah now thats usefull for this.
@AM: have you considered encoding the lot before you store it?

[aliasmask]

Yes, I've considered it and tried it but encoding/decoding IMO adds too much time to processing.

[badsequel]

Give me a way to connect a sqlite database to maptool and a few functions to get sql statements running on it and I will make much merry! /Offtopic

[aliasmask]

I used sql the first time to build my indexes for my spell library where the results were the table index numbers. That's one reason why it was a pain to update (not being integrated with MT). This time around I'm using the property names and no MT tables.