Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are
spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the
password reset link.
Due to spam on this forum, all posts now need moderator approval.
Entire forum
➜ MUSHclient
➜ VBscript
➜ Handling numerous speedwalks - and using a database
|
Handling numerous speedwalks - and using a database
|
It is now over 60 days since the last post. This thread is closed.
Refresh page
Pages: 1
2
3 4
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #30 on Sat 07 Sep 2002 10:40 PM (UTC) |
| Message
| Type 4 uses the Access 97 engine, type 5 uses the Access 2000 engine. Which is best really depends on which engine you have installed, however type 4 is more likely to be compatible with a greater number of users. I'm not sure what would happen if you left the type out, it might default to whatever is installed.
As for creating the table, at the end of the day the database engine gets the SQL command to create it, however using the VB COM objects abstracts that away from you a bit, the end result will be the same.
As I have a book on SQL I find it simpler to use the actual commands, rather than reading all the MS documentation to work out what to do with their COM objects to achieve the result I want anyway. |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
| Posted by
| Shadowfyr
USA (1,791 posts) Bio
|
| Date
| Reply #31 on Sun 08 Sep 2002 07:42 AM (UTC) |
| Message
| One thing I am not sure of... One older windows installs the Jet controls may not be v4.0 and newer updates of them could end up being v4.1 or even 5.0, etc. I haven't looked at the ADOX stuff and the like too closely yet (hard to find good docs on some of this stuff), but is it possible that for compatibility purposes our plugins should use the ADOX or ADODB catalog features to get the correct Jet version string to use in accessing it, before trying to use the other commands?
Seems to me this may be a good idea or some systems may reject them due to version conflicts. Just a thought. ;) | | Top |
|
| Posted by
| Magnum
Canada (580 posts) Bio
|
| Date
| Reply #32 on Mon 09 Sep 2002 05:07 PM (UTC) |
| Message
| I've been trying to build a database using MS Access. No VBS programming or anything else (yet). This question doesn't really belong here since it isn't MUSHclient related, but I'll ask anyway. Once I get a database built and working smoothly in Access, I'll attempt to build a MUSHclient scripted interface to it.
I have one field called "Weapon Damage Types", and I've set it as a multi-select listbox (Extended). There are nine options in the listbox.
I've build a form for inputting data. The problem is the listbox. When I click any of the items in the listbox, they are highlighted, but if I move to a different record and then come back, the items in the listbox are no longer highlighted. I want them to be. Indeed, if I move away from that record again, I don't know if Access updates the record to have none selected.
I've tried using a "Requery" macro in several places, but I just can't seem to get it to work the way I want. Any suggestions? |
Get my plugins here: http://www.magnumsworld.com/muds/
Constantly proving I don't know what I am doing...
Magnum. | | Top |
|
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #33 on Tue 10 Sep 2002 05:40 AM (UTC) |
| Message
| Hmm - personally I wouldn't requery at all, as that would refresh the original data.
Hard to say without seeing the database. If it isn't too big zip it up and email it to me. |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #34 on Wed 11 Sep 2002 03:57 AM (UTC) |
| Message
| OK, I see what is happening, sort of. :)
You have a listbox where you can make multiple selections (eg. Acid and Magical) however you are storing the results into a single integer.
I can't see how multiple items can fit into one number. Either:
- Make it a combo box (like weapon quality) if you only really want one; or
- Store it as a bitmap into the database. You would need to have an event hander (eg. onchange or something) that uses the selected items to construct a bitmap (eg. acid = 1, Asphyxiation = 2, Burning = 4, Crushing = 8 and so on), adding them together and then storing the result in the database.
I'm not sure if Access has some automatic way of doing that.
|
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
| Posted by
| Magnum
Canada (580 posts) Bio
|
| Date
| Reply #35 on Wed 11 Sep 2002 04:06 PM (UTC) |
| Message
| Well, more tham just being visually appealing, the database should actually sore the multiply selected values.
It is possible in the game for weapons to do multiple types of damage, which is why that particular field is configured as a listbox.
I noticed that the internal result was being stored as a byte. I can't figure out how to change that. Access picks the field type when you go through the automaked "lookup" process.
I've spent much time already, searching the internet for an answer... and haven't been able to find one.
I'm not sure how I'll work around this problem, but I don't like the bitmap idea. As I said earlier, once this works smoothly in Access itself, I plan to pull values with a MUSHclient script, and trying to process a bitmap would be near impossible.
I appreciate your help anyway, Nick. :) |
Get my plugins here: http://www.magnumsworld.com/muds/
Constantly proving I don't know what I am doing...
Magnum. | | Top |
|
| Posted by
| Magnum
Canada (580 posts) Bio
|
| Date
| Reply #36 on Wed 11 Sep 2002 04:15 PM (UTC) |
| Message
| Heh, I'm a dolt sometimes. Thought you meant a graphic bitmap, till I reviewed my own posting, and saw my idiocy.
Yeah, your idea is good. I considered it myself too. First, I wanted to see if there was some straight forward way of handling this issue. Microsoft provides the option of using a listbox, I figure surely they have an automated way of storing the selected data!
At times, the "Requery" macro seemed to work, but problems would arise because of the timing of the refresh. I would often send Access into an infinite loop as it requeried repetitevly, or it would requery and update properly, but then immediately lose the displayed values.
Somewhere I came upon a knowledgebase article (regarding a previous version of Access), noting this problem, but there was no work-around provided. From what I gather, the multiply selected data IS stored, but the form presents the listbox as blank every time it is refreshed. Absolutely un-intuitive and stupid, if ya ask me.
I'll look into this more later, it's low priority for now. Any help is still appreciated. :) |
Get my plugins here: http://www.magnumsworld.com/muds/
Constantly proving I don't know what I am doing...
Magnum. | | Top |
|
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #37 on Wed 11 Sep 2002 10:55 PM (UTC) Amended on Wed 11 Sep 2002 10:57 PM (UTC) by Nick Gammon
|
| Message
| Now that I think about it, the problem is really one of database design. ;)
Even using a bitmap you are limiting yourself to (say) 32 damage types per item, which may be fine for you, but is still a limitation. Strictly speaking, in database design, you are not supposed to store "repeated values" in a row. In your case, multiple damage types are repeated values.
Thus, I would design it like this:
AOD Items table
ItemID - int, autoincrement, primary key
Item Name - name of item
Item Type Key - Location where this item is equipped.
(and so on)
I would use an item "id" so that if you change the name of the item its primary key doesn't change (eg. if you misspell "An iron hand-axe"). The primary key is important as we will see in a moment.
Weapon damage types table
Weapon Damage Index - int, autoincrement, primary key
Weapon Damage Label - text
So far, so good.
Now the important bit, you need to "link" the many-to-many relationship, of multiple items that can have multiple damage types using a third table ...
Item/damage type table
ItemID - int
Weapon Damage Index - int
Primary key: combination of Weapon Damage Index and ItemID
Thus, every row in this third table represents a damage type for a particular item.
Say you had an item "1-An iron hand-axe" that has damage types: 5-cutting and 9-piercing then you would have two records:
1, 5 (item 1 has damage type 5)
1, 9 (item 1 has damage type 9)
By making the primary key the combination you guarantee that each item in the third table is unique (they must be unique in the original tables, and the combination is therefore unique).
Now, finally, to present it visually. Make a third form for the third table (eg. use combo-boxes), and then make this third form a sub-form of the item form.
What this will do, with very little effort on your part, is to let you add any number of damage items (separate rows) for any item. Do a "new record" in the sub-form to add a damage item, or "delete record" to remove a damage item. |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
| Posted by
| Shadowfyr
USA (1,791 posts) Bio
|
| Date
| Reply #38 on Thu 12 Sep 2002 12:23 AM (UTC) |
| Message
| Way to complex there Nick. In general it is not an issue anyway. Muds need to have a set number of damage types so that mobs and weapons can be matched to determine who gets hurt. As such there is little change that a mob be added to the game which suddenly aquired 'plasma' damage or vulnerability to the same, for example, without needing to recode a large part of the muds core code. It seems unlikely to me that any mud would have more that 16-20 'true' damage types total, though they may use any number of names to describe them. This is basically the case here.
Also, while redundant info is technically bad, in some cases it is a null issue, since you can much more easilly search for a 'crushing' type than worry that you may have entered 'cruhing' someplace. The damage type table would be small, fixed in size and since weapons 'could' have multiple types, you end up storing a non-fixed number of indexes to the table. This is worse, since instead of worrying that you have 17 types, instead of the 16 you get with an unsigned integer, you have to worry about your table only allowing 4 types per weapon and some new weapon having 5 types. It is usually easier to allow an extra integer for spill over, in case of having more than you expect, than adding a new field.
Ironically this would be even easier is COBOL, where records are just raw data, and you create the actually field definitions in the program accessing the database. You can thus add padding bytes where you think the fields may outgrow the expected contents from the start, just in case you needed to change it later. I have done that with binary file access under QBasic, but most so called modern databases don't (as far as I know) allow you to re-cast the table contents using a different set of fields. A bit of a flaw imho, since your program has to regenerate the entire file to effect the proper changes.
Anyway, in this case a bit-flag system will work best. | | Top |
|
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #39 on Thu 12 Sep 2002 02:42 AM (UTC) |
| Message
|
Quote:
The damage type table would be small, fixed in size and since weapons 'could' have multiple types, you end up storing a non-fixed number of indexes to the table. This is worse, since instead of worrying that you have 17 types, instead of the 16 you get with an unsigned integer, you have to worry about your table only allowing 4 types per weapon and some new weapon having 5 types.
I agree that maybe a "bitmap" would be best if you have a fairly low number of types, although it makes it more complicated in the form, however I don't understand the above point. I wasn't suggesting a "table of integers" inside the existing record, but a separate table, which being its own database table can have unlimited size, allowing for nil to any number of damage types per weapon.
eg., assuming each line is a database record:
1, Sword <-- weapon table
2, Machete <-- weapon table
1, Cutting <-- damage type table
2, Pierce <-- damage type table
3, Slice <-- damage type table
1, 2 <-- weapon/damage table - sword pierces
1, 3 <-- weapon/damage table - sword slices
2, 1 <-- weapon/damage table - machete cuts
Now if you want to find which weapon slices, you search for records in the weapon/damage where the damage type = 3, and using a (SQL) join find which weapon it is. ie., in the example above, a search for damage type 3 gives you the fact that it is weapon type 1, which you then lookup the weapon table to see it is a sword. This sounds complicated, but is a simple SQL statement. Off the top of my head it would be:
SELECT * from weapon, damage_type
WHERE damage_type.damage_id = 3
AND damage_type.damage_id = weapon.damage_id
AND damage_type.weapon_id = weapon.weapon_id
ORDER BY weapon.name
|
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
| Posted by
| Shadowfyr
USA (1,791 posts) Bio
|
| Date
| Reply #40 on Thu 12 Sep 2002 03:23 AM (UTC) Amended on Thu 12 Sep 2002 03:24 AM (UTC) by Shadowfyr
|
| Message
| Yeah. I wasn't thinking of it that way. That would work, but is perhaps more appropriate for non-fixed information. I.e. If you expect to add new data, then it is useful, otherwise a database is a waste of resources and time, since it takes longer to look up a record through that method than match to a fixed list using a built in array. Using a complete database program you probably are not given a choice, since there is likely no way to store such a table in anything other than the database itself. However, when using your own custom script or program and a fixed table, there is no point in adding extra overhead by looking up just the name of a damage type in a third table. But otherwise you are correct, it is more flexible, despite being overkill on most muds.
I am not exactly sure why the problem Magnum is having happens though. If I was using a list box, I might consider using a list of possibles, then a second window I could 'add' the items to, then for plain lookup, just the list of what is actually in there. Not to mention my own code for displaying it. A good DB program should let you do so, but MS probably went the 'let the program do it all' route that they so enjoy screwing us up with. lol | | Top |
|
| Posted by
| Jeffrey F. Pia
(49 posts) Bio
|
| Date
| Reply #41 on Thu 12 Sep 2002 03:42 PM (UTC) Amended on Thu 12 Sep 2002 09:56 PM (UTC) by Nick Gammon
|
| Message
|
Quote:
1, Sword <-- weapon table
2, Machete <-- weapon table
1, Cutting <-- damage type table
2, Pierce <-- damage type table
3, Slice <-- damage type table
1, 2 <-- weapon/damage table - sword pierces
1, 3 <-- weapon/damage table - sword slices
2, 1 <-- weapon/damage table - machete cuts
I'm no whiz at DB design (which is why all my SWs are still sitting in an Excel spreadsheet), but I would like to learn... why would the above 3 tables be more efficient than one table? For example, these records:
Wpn table
Sword, Pierce
Sword, Slice
Machete, Cutting
Could be accessed with this SQL code:
SELECT * from Wpn
WHERE Wpn.type = "Sword"
ORDER BY Wpn.type
I think the point I'm missing is in regards to redundant data. Is the association of text strings to integers as IDs just to save space? | | Top |
|
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #42 on Thu 12 Sep 2002 09:55 PM (UTC) |
| Message
| I'm not worried about saving a few bytes here and there. Your design has a few problems ...
Quote:
Wpn table
Sword, Pierce
Sword, Slice
Machete, Cutting
- Do you have one sword, that pierces and cuts, or two swords, one that pierces and one that cuts?
- If you misspell something (eg. "sword" as "sord") then you may have to correct it in multiple places.
- As you are entering damage as a string you may enter it differently in different places (eg. "pierces" and "pierce") which makes it hard to find all weapons that pierce.
- If you had two swords, both that pierce, how would you delete one of them? If you did this:
DELETE FROM from Wpn
WHERE Wpn.type = "Sword"
AND Wpn.damage = "Pierce"
you would delete both of them. That is what the unique IDs are for.
- How do you quickly see a list of all possible damage types? By storing them in the main table as they are used you don't have a list anywhere of the unused ones. Thus in your short example, if there was a damage type of "magic" you have no way of presenting that to the user as the damage type "magic" is not used yet.
My solution may seem complex but it does address those issues. :) |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
| Posted by
| Shadowfyr
USA (1,791 posts) Bio
|
| Date
| Reply #43 on Thu 12 Sep 2002 09:56 PM (UTC) Amended on Thu 12 Sep 2002 09:57 PM (UTC) by Shadowfyr
|
| Message
| Well we are looking at this situation (and most muds would use something similar):
Weapon Damage Type(s)
------------ ------------------
Zeldan's Flameblade Heat, Edge, Magic
The solutions to this is one of the following:
Bit-flag method - Stores type in an unsigned integer where the types are:
1 - Edged
2 - Blunt
4 - Pierce
8 - Heat
16 - Cold
32 - Poison
64 - Mind
128 - Magic
256 - Asphyxiation
512 - Acid
So The above weapon would be stored as "Zelden's Flameblade,137" and the actually types would be returned from an array in the script, where the element in the array is the 'bit position' used to store that type.
Two Tables - Also uses an array in the script, but would use the following:
Table 1: Weapons
Weapon Weapon Number
------------ ------------------
Zeldan's Flameblade 1
Table 2: Types
Index to the Weapon Damage Type
------------------- ------------------
1 3
1 0
1 7
You look up the weapon to get the number, then use the number to get the damage index and use it to look up the name in the array.
Three Tables - Pointless, unless you are actually using Access or the like and "can't" store the names in a faster array structure. It is also quite slow, since you have to ask the database for the weapon number, then ask it to look up each type assigned to the weapon and then finally ask it for each of the names for those retrieved types. This wastes a lot of time and is even worse when used from scripting, since ADODB access is twice as slow as the same look up performed by a compiled program through direct database API calls.
The first option is 'always' better, if you know that the thing you are going to look for won't change, even if you have to use a long integer to store all the possible combos. The second and third options are best left for if you don't know for certain how many types you have to worry about and even then, you are better off to use the third option to 'store' the names, but the second option to use them by loading the types into a global array (thus avoiding having to perform multiple ADODB searches each time for the names).
If I planned to do that I would use a OnPluginConnect sub to load all the types into a mushclient variable, padding them to equal lengths, then just multiply the type number by that length to get the name. Doing so would eliminate the time taken to look up each type when reading a record, while also avoid the hassle created by having to split the string into an array every time. If there where like 100+ types though, you wouldn't have much choice but the use the database. :p
Basically I agree that one table is always better, but it depends on how complex the situation is and your version assumes that a weapon only does one type of damage, which is not a good assumption to make. Even some types of normal swords can do slashing 'edge' attacks, blunt trauma and piercing damage. It just depends how you hit someone with it. ;) | | Top |
|
| Posted by
| Nick Gammon
Australia (23,165 posts) Bio
Forum Administrator |
| Date
| Reply #44 on Thu 12 Sep 2002 10:01 PM (UTC) |
| Message
|
Quote:
It is also quite slow, since you have to ask the database for the weapon number, then ask it to look up each type assigned to the weapon and then finally ask it for each of the names for those retrieved types.
It's not that bad, as you get all those pieces of information in a single query (joining the tables) and thus only do one ODBC call, not three. Also, the database server should be caching frequently-accessed and small pieces of data, so the time needed to do those joins could be quite small.
The reason I suggested the third table is that Magnum was having trouble making a form that used the two tables, but basically had a many-to-many relationship which isn't supported in third-normal form with two tables, without mucking around. The third table does things the "correct" way (in database design) and thus can be implemented simply in an Access form. |
- Nick Gammon
www.gammon.com.au, www.mushclient.com | | Top |
|
The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).
To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.
165,971 views.
This is page 3, subject is 4 pages long: ![[Previous page]](/images/thin_prev.gif)
1
2
3 4
![[Next page]](/images/thin_next.gif)
It is now over 60 days since the last post. This thread is closed.
Refresh page
![[Go to top]](/images/gototop.gif)
top